if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_SelfMotionaddDataToTmpDep')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_SelfMotionaddDataToTmpDep
GO
CREATE PROCEDURE bd_proc_SelfMotionaddDataToTmpDep --自动同步部门添加到系统临时表中

AS
	SET NOCOUNT ON;

	INSERT into tmp_dt_dep
	select DISTINCT BRANCH,-1,BRANCH_DESCRIPTION from syn_server.scm_main_jczx.dbo.bd_ehrDepData a
	where NOT exists(SELECT 1 FROM tmp_dt_dep b WHERE a.BRANCH = b.dep_no_add AND a.BRANCH_DESCRIPTION = b.dep_name);

	INSERT into tmp_dt_dep
	select DISTINCT DEPARTMENT,BRANCH,DEPARTMENT_DESCRIPTION from syn_server.scm_main_jczx.dbo.bd_ehrDepData a
	where NOT exists(SELECT 1 FROM tmp_dt_dep b WHERE a.DEPARTMENT = b.dep_no_add AND a.BRANCH = b.dep_no_parent AND a.DEPARTMENT_DESCRIPTION = b.dep_name);

	INSERT into tmp_dt_dep
	select DISTINCT SECTION,DEPARTMENT,SECTION_DESCRIPTION from syn_server.scm_main_jczx.dbo.bd_ehrDepData a
	where NOT exists(SELECT 1 FROM tmp_dt_dep b WHERE a.SECTION = b.dep_no_add AND a.DEPARTMENT = b.dep_no_parent AND a.SECTION_DESCRIPTION = b.dep_name);

	INSERT into tmp_dt_dep
	select DISTINCT SUB_SECTION,SECTION,SUB_DESCRIPTION from syn_server.scm_main_jczx.dbo.bd_ehrDepData a
	where NOT exists(SELECT 1 FROM tmp_dt_dep b WHERE a.SUB_SECTION = b.dep_no_add AND a.SECTION = b.dep_no_parent AND a.SUB_DESCRIPTION = b.dep_name);

	INSERT into tmp_dt_dep
	select DISTINCT CITY_CODE,SUB_SECTION,CITY_DESCRIPTION from syn_server.scm_main_jczx.dbo.bd_ehrDepData a
	where NOT exists(SELECT 1 FROM tmp_dt_dep b WHERE a.CITY_CODE = b.dep_no_add AND a.SUB_SECTION = b.dep_no_parent AND a.CITY_DESCRIPTION = b.dep_name);


  SET NOCOUNT OFF;
